-- ===============================================
-- CONFIG Adventure Works Cycles Database
-- 


/*=====================================================================

  File:      AWStore.sql for Adventure Works Cycles Storefront Sample
  Summary:   Defines the stored procedures for the Storefront application.
  Date:      June 30, 2003

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) 2003 Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */

-- point to proper DB 
USE AdventureWorks
GO
IF (SELECT TOP 1 VersionDate FROM dbo.AWBuildVersion) < CONVERT(DATETIME, '20060105', 101) 
  PRINT 'This sample requires the SQL Server 2005 SP1 or later version of the AdventureWorks database.  '
	  + 'Please install it from the downloads section at http://msdn.microsoft.com/sql/';

IF NOT EXISTS (SELECT * FROM sys.objects WHERE ([name] = N'ConvertCurrency') AND ([type] = 'FS'))
PRINT 'You must install the AdventureWorks CLR Integration Engine Sample from 
drive:\Program Files\Microsoft SQL Server\100\Samples\Engine\Programmability\CLR\AdventureWorks
before installing this sample.'
GO

-- =======================================================
-- MAKE TABLES AND SPROCS FOR Adventure Works Store DB
-- =======================================================

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CustomerAdd') 
DROP PROCEDURE dbo.usp_CustomerAdd;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CustomerAddSurvey')
DROP PROCEDURE dbo.usp_CustomerAddSurvey;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CustomerUpdate')
DROP PROCEDURE dbo.usp_CustomerUpdate;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CustomerAlsoBought')
DROP PROCEDURE dbo.usp_CustomerAlsoBought;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CustomerDetail')
DROP PROCEDURE dbo.usp_CustomerDetail;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CustomerLogin')
DROP PROCEDURE dbo.usp_CustomerLogin;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CustomerAllStateProvince')
DROP PROCEDURE dbo.usp_CustomerAllStateProvince;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CustomerDefaultAddresses')
DROP PROCEDURE dbo.usp_CustomerDefaultAddresses;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CustomerAccessAddress')
DROP PROCEDURE dbo.usp_CustomerAccessAddress;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_OrdersAdd')
DROP PROCEDURE dbo.usp_OrdersAdd;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_OrdersAddresses')
DROP PROCEDURE dbo.usp_OrdersAddresses;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_OrdersDetail')
DROP PROCEDURE dbo.usp_OrdersDetail;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_OrdersList')
DROP PROCEDURE dbo.usp_OrdersList;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_OrdersShippingMethods')
DROP PROCEDURE dbo.usp_OrdersShippingMethods;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ProductCategoryList')
DROP PROCEDURE dbo.usp_ProductCategoryList;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ProductDetail')
DROP PROCEDURE dbo.usp_ProductDetail;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ProductSearch')
DROP PROCEDURE dbo.usp_ProductSearch;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ProductsBySubcategory')
DROP PROCEDURE dbo.usp_ProductsBySubcategory;
GO


IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ProductsMostPopular')
DROP PROCEDURE dbo.usp_ProductsMostPopular;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ReviewsAdd')
DROP PROCEDURE dbo.usp_ReviewsAdd;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ReviewsList')
DROP PROCEDURE dbo.usp_ReviewsList;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ReviewsTopID')
DROP PROCEDURE dbo.usp_ReviewsTopID;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ReviewsLatest')
DROP PROCEDURE dbo.usp_ReviewsLatest;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingCartAddItem')
DROP PROCEDURE dbo.usp_ShoppingCartAddItem;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingCartEmpty')
DROP PROCEDURE dbo.usp_ShoppingCartEmpty;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingCartItemCount')
DROP PROCEDURE dbo.usp_ShoppingCartItemCount;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingList')
DROP PROCEDURE dbo.usp_ShoppingList;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_SetShoppingCartID')
DROP PROCEDURE dbo.usp_SetShoppingCartID;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingCartRemoveAbandoned')
DROP PROCEDURE dbo.usp_ShoppingCartRemoveAbandoned;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingCartRemoveItem')
DROP PROCEDURE dbo.usp_ShoppingCartRemoveItem;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingCartTotal')
DROP PROCEDURE dbo.usp_ShoppingCartTotal;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingCartUpdate')
DROP PROCEDURE dbo.usp_ShoppingCartUpdate;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingCartListTopModels')
DROP PROCEDURE usp_ShoppingCartListTopModels;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_ShoppingCartGetProductRecommendations')
DROP PROCEDURE dbo.usp_ShoppingCartGetProductRecommendations;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = N'StoreServerComponents')
DROP ASSEMBLY StoreServerComponents;
GO



-- Lab stored procedures

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'GetShoppingCartTotal')
    DROP PROCEDURE [dbo].[GetShoppingCartTotal];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'GetShoppingCartList')
    DROP PROCEDURE [dbo].[GetShoppingCartList];

IF EXISTS (SELECT * FROM sys.objects WHERE ([name] = N'GetStock') AND ([type] = 'FN')) 
    DROP FUNCTION [dbo].[GetStock];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'DebitStockLevel')
    DROP PROCEDURE [dbo].[DebitStockLevel];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'GetStockLevel')
    DROP PROCEDURE [dbo].[GetStockLevel];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'GetMakeFlag')
    DROP PROCEDURE [dbo].[GetMakeFlag];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'UpdateOrderStatus')
    DROP PROCEDURE [dbo].[UpdateOrderStatus];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'GetCustomerInfo')
    DROP PROCEDURE [dbo].[GetCustomerInfo];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'UpdateOrderState')
    DROP PROCEDURE [dbo].[UpdateOrderState];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'GetOrderState')
    DROP PROCEDURE [dbo].[GetOrderState];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'StoreMessage')
    DROP PROCEDURE [dbo].[StoreMessage];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'UpdateItemState')
    DROP PROCEDURE [dbo].[UpdateItemState];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'GetItemState')
    DROP PROCEDURE [dbo].[GetItemState];

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'StoreProcessingInfo')
    DROP PROCEDURE [dbo].StoreProcessingInfo


GO

----------------------------------------------------------
-- Drop tables
----------------------------------------------------------


-- Drop table for storing shipping request message
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'ShippingRequestStore')
DROP TABLE [dbo].[ShippingRequestStore];

-- Drop OrderState table
IF EXISTS(SELECT * FROM sys.tables WHERE name=N'OrderState')
    DROP TABLE OrderState;

-- Drop item state table
IF EXISTS(SELECT * FROM sys.tables WHERE name=N'ItemState')
    DROP TABLE ItemState;

-- Drop processing information  table
IF EXISTS(SELECT * from sys.tables where name=N'ProcessingLog')
    DROP TABLE ProcessingLog;


-- Drop StatusCode table
IF EXISTS(SELECT * FROM sys.tables where name=N'StatusCodeMessage')
    DROP TABLE StatusCodeMessage;
GO

-- Drop audit table
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'OrderingAudit') 
DROP TABLE OrderingAudit;
GO

USE master
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE [name] = 'UnsafeSample_Login')
DROP LOGIN UnsafeSample_Login;
GO

IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE [name] = 'UnsafeSample_Key')
DROP ASYMMETRIC KEY UnsafeSample_Key;
GO

--Before we register the assembly to SQL Server, we must arrange for the appropriate permissions.
--Assemblies with unsafe or external_access permissions can only be registered and operate correctly
--if either the database trustworthy bit is set or if the assembly is signed with a key,
--that key is registered with SQL Server, a server principal is created from that key,
--and that principal is granted the external access or unsafe assembly permission.  We choose
--the latter approach as it is more granular, and therefore safer.  You should never
--register an assembly with SQL Server (especially with external_access or unsafe permissions) without
--thoroughly reviewing the source code of the assembly to make sure that its actions 
--do not pose an operational or security risk for your site.

DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
SELECT @SamplesPath = substring(physical_name, 1, 
  patindex('%Microsoft SQL Server%', physical_name) - 1) 
  + 'Microsoft SQL Server\100\Samples\Integrated Samples\' 
FROM master.sys.database_files 
WHERE name = 'master';

EXEC('CREATE ASYMMETRIC KEY UnsafeSample_Key FROM EXECUTABLE FILE = ''' + @SamplesPath + 'Storefront\CS\StoreServerComponents\bin\debug\StoreServerComponents.dll'';');
CREATE LOGIN UnsafeSample_Login FROM ASYMMETRIC KEY UnsafeSample_Key
GRANT UNSAFE ASSEMBLY TO UnsafeSample_Login
GO

USE AdventureWorks
GO
-- =============================================================
-- Register the server-side CLR assembly
-- =============================================================

-- Add the assembly which contains the CLR methods we want to invoke on the server.
DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
SELECT @SamplesPath = substring(physical_name, 1, 
  patindex('%Microsoft SQL Server%', physical_name) - 1) 
  + 'Microsoft SQL Server\100\Samples\Integrated Samples\' 
FROM master.sys.database_files 
WHERE name = 'master';

CREATE ASSEMBLY StoreServerComponents FROM @SamplesPath + 'Storefront\CS\StoreServerComponents\bin\debug\StoreServerComponents.dll'
WITH permission_set = Unsafe;
GO



-- =============================================================
-- create the Stored procs
-- =============================================================

-- Used as part of the registration process for the store.
-- Inserts information about the customer into the Customer and Individual tables.

CREATE PROCEDURE usp_CustomerAdd
(
    @FirstName  nvarchar(50),
    @LastName   nvarchar(50),
    @Email      nvarchar(50),
    @PasswordHash   varchar(128),
    @PasswordSalt   varchar(10),
    @CustomerID int OUTPUT
)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

DECLARE @ContactID int
DECLARE @err int

BEGIN TRANSACTION;

BEGIN TRY

INSERT INTO Person.Contact
(
    FirstName,
    LastName,
    EmailAddress,
    PasswordHash,
    PasswordSalt
)
VALUES
(
    @FirstName,
    @LastName,
    @Email,
    @PasswordHash,
    @PasswordSalt
)

SET @ContactID = SCOPE_IDENTITY();

INSERT INTO Sales.Customer
(
    CustomerType
)

VALUES
(
    N'I'  -- An individual
);

SET @CustomerID = SCOPE_IDENTITY();

INSERT INTO Sales.Individual
(
    ContactID,
    CustomerID
)

VALUES
(
    @ContactID,
    @CustomerID
);

END TRY
BEGIN CATCH
 SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
	RETURN(1);
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
	RETURN(0);
GO


-- Insert the answers to questions asked during the registration process.

CREATE PROCEDURE usp_CustomerAddSurvey
(
    @CustomerID int,
    @CustomerSurvey nvarchar(2048)
)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

UPDATE Sales.Individual
-- Find the five most common additional products purchased along with a given product.
SET Demographics = @CustomerSurvey
WHERE CustomerID = @CustomerID;
GO


CREATE PROCEDURE usp_CustomerAlsoBought
(
    @ProductID int
)
AS
SET NOCOUNT ON;

/* We want to take the top 5 products contained in
    the orders where someone has purchased the given Product */
SELECT  TOP 5 
    SOD.ProductID,
    P.[Name],
    SUM(SOD.OrderQty) as TotalNum
FROM 
    Sales.SalesOrderDetail AS SOD
    INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
WHERE SOD.SalesOrderID IN 
(
    /* This inner query should retrieve all orders that have contained the productID */
    SELECT DISTINCT SalesOrderID 
    FROM Sales.SalesOrderDetail
    WHERE ProductID = @ProductID
)
AND SOD.ProductID != @ProductID 
GROUP BY SOD.ProductID, P.[Name] 
ORDER BY TotalNum DESC;
GO

CREATE PROCEDURE usp_CustomerUpdate
(
    @CustomerID int,
    @FirstName  nvarchar(50),
    @LastName   nvarchar(50),
    @Email      nvarchar(50),
    @PasswordHash   varchar(128),
    @EmailPromotion int
)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

DECLARE @err int

BEGIN TRANSACTION;

UPDATE Person.Contact 
SET
    FirstName = @FirstName,
    LastName = @LastName,
    EmailAddress = @Email,
    EmailPromotion = @EmailPromotion
FROM Sales.Individual as I 
WHERE I.ContactID = Person.Contact.ContactID AND I.CustomerID = @CustomerID;

SELECT
    @err = @@error;
    
IF (@err <> 0) GOTO abort;

IF (@PasswordHash <> '')
BEGIN
    UPDATE
        Person.Contact 
    SET
        PasswordHash = @PasswordHash
    FROM Sales.Individual as I 
    WHERE I.ContactID = Person.Contact.ContactID AND I.CustomerID = @CustomerID;

    SELECT
        @err = @@error;
    
    IF (@err <> 0) GOTO abort;
END

COMMIT TRANSACTION;
RETURN(0);

abort:
ROLLBACK TRANSACTION;
RETURN(@err);   

GO

-- Return specific information (including information necessary to authenticate) 
-- about the specified customer. 

CREATE PROCEDURE usp_CustomerDetail
(
    @CustomerID int,
    @FirstName   nvarchar(50) OUTPUT,
    @LastName   nvarchar(50) OUTPUT,
    @Email      nvarchar(50) OUTPUT,
    @PasswordHash   varchar(128) OUTPUT,
    @PasswordSalt   varchar(10) OUTPUT,
    @EmailPromotion int OUTPUT
)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

SELECT 
    @FirstName = FirstName, 
    @LastName = LastName,
    @Email    = EmailAddress, 
    @PasswordHash = PasswordHash,
    @PasswordSalt = PasswordSalt,
    @EmailPromotion = EmailPromotion
FROM 
    Person.Contact as C
JOIN Sales.Individual as I
ON I.ContactID = C.ContactID
WHERE 
    I.CustomerID = @CustomerID;
GO

-- Lookup the Customer's ID number based on her email address.  Return zero
-- if the email address is not registered.

CREATE PROCEDURE usp_CustomerLogin
(
    @Email      nvarchar(50),
    @CustomerID int OUTPUT
)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

SELECT 
    @CustomerID = CustomerID
FROM 
    Sales.Individual AS I
INNER JOIN Person.Contact AS C
ON I.ContactID = C.ContactID
WHERE 
    C.EmailAddress = @Email;

IF @@Rowcount < 1 
SELECT 
    @CustomerID = 0;
GO

-- Returns the bill to and ship to addresses as single rows in two distinct result sets.
CREATE PROCEDURE usp_CustomerAllStateProvince
AS
SET NOCOUNT ON;

SELECT 
    SP.StateProvinceID, SP.[Name] + ' (' + CR.[Name] + ')' AS [Name]
FROM 
    Person.StateProvince as SP
    JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode
ORDER BY 
    CR.[Name], SP.[Name] ASC;
GO

-- Returns the default billing and shipping addresses as two result sets.  The default is the last
-- addresses used in the most recent sales order.
CREATE PROCEDURE dbo.usp_CustomerDefaultAddresses
(
    @CustomerID int
)
AS
SET NOCOUNT ON;

SELECT 
    TOP 1 A.AddressID, A.AddressLine1, A.AddressLine2, A.City, A.StateProvinceID, 
    SP.[Name] + ' (' + CR.[Name] + ')' AS StateProvinceName, PostalCode
FROM 
    Sales.SalesOrderHeader as SOH
    JOIN Person.Address as A ON SOH.BillToAddressID = A.AddressID
    JOIN Person.StateProvince as SP ON A.StateProvinceID = SP.StateProvinceID
    JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode
WHERE 
    SOH.CustomerID = @CustomerID
ORDER BY 
    SOH.ModifiedDate DESC;

SELECT 
    TOP 1 A.AddressID, A.AddressLine1, A.AddressLine2, A.City, A.StateProvinceID, 
    SP.[Name] + ' (' + CR.[Name] + ')' AS StateProvinceName, PostalCode
FROM 
    Sales.SalesOrderHeader as SOH
    JOIN Person.Address as A ON SOH.ShipToAddressID = A.AddressID
    JOIN Person.StateProvince as SP ON A.StateProvinceID = SP.StateProvinceID
    JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode
WHERE 
    SOH.CustomerID = @CustomerID
ORDER BY 
    SOH.ModifiedDate DESC;
GO

-- Attempts to locate the specified address in the database.  If it is located, the 
-- AddressID is returned as an output parameter.  Otherwise, a new Address row is inserted and
-- the AddressID of the new address is returned.
CREATE PROCEDURE usp_CustomerAccessAddress
(
    @CustomerID         int,
    @AddressTypeName    nvarchar(50),   
    @AddressLine1   nvarchar(60),
    @AddressLine2   nvarchar(60),
    @City           nvarchar(30),
    @StateProvinceID int,
    @PostalCode     nvarchar(15),
    @AddressID      int OUTPUT
)
AS
    SET NOCOUNT ON;

    DECLARE @err int
    DECLARE @AddressTypeID int

    SELECT 
        TOP 1 @AddressID = AddressID
    FROM 
        Person.Address
    WHERE
        AddressLine1 = @AddressLine1 AND AddressLine2 = @AddressLine2 AND
        City = @City AND PostalCode = @PostalCode
    ORDER BY
        ModifiedDate;

    if (@@RowCount = 0)
    BEGIN
        BEGIN TRANSACTION;
        INSERT INTO Person.Address 
        (
            AddressLine1, 
            AddressLine2, 
            City, 
            StateProvinceID, 
            PostalCode
        )
        VALUES
        (
            @AddressLine1,
            @AddressLine2,
            @City,
            @StateProvinceID,
            @PostalCode
        );

        SELECT 
            @AddressID = SCOPE_IDENTITY(),
            @err = @@error;

        if (@err <> 0) GOTO abort;

        SELECT
            @AddressTypeID = AddressTypeID
        FROM
            Person.AddressType
        WHERE
            [Name] = @AddressTypeName;

        if (@@error <> 0 OR @@RowCount < 1) GOTO abort;
        
        INSERT INTO Sales.CustomerAddress
        (
            CustomerID,
            AddressID,
            AddressTypeID
        )
        VALUES
        (
            @CustomerID,
            @AddressID,
            @AddressTypeID
        )

        SET @err = @@error;
        if (@err <> 0) GOTO abort;

        COMMIT TRANSACTION;
        RETURN(0);

        abort:
        ROLLBACK TRANSACTION;
        RETURN(@err);

    END
GO

-- Returns the billing and shipping addresses of the specified sales order as two result sets.  
CREATE PROCEDURE dbo.usp_OrdersAddresses
(
    @OrderID    int
)
AS
SET NOCOUNT ON;

SELECT 
    A.AddressID, A.AddressLine1, A.AddressLine2, A.City, A.StateProvinceID, 
    SP.[Name] + ' (' + CR.[Name] + ')' AS StateProvinceName, PostalCode
FROM 
    Sales.SalesOrderHeader as SOH
    INNER JOIN Person.Address as A ON SOH.BillToAddressID = A.AddressID
    INNER JOIN Person.StateProvince as SP ON A.StateProvinceID = SP.StateProvinceID
    INNER JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode
WHERE 
    SOH.SalesOrderID = @OrderID

SELECT 
    A.AddressID, A.AddressLine1, A.AddressLine2, A.City, A.StateProvinceID, 
    SP.[Name] + ' (' + CR.[Name] + ')' AS StateProvinceName, PostalCode
FROM 
    Sales.SalesOrderHeader as SOH
    INNER JOIN Person.Address as A ON SOH.ShipToAddressID = A.AddressID
    INNER JOIN Person.StateProvince as SP ON A.StateProvinceID = SP.StateProvinceID
    INNER JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode
WHERE 
    SOH.SalesOrderID = @OrderID
GO

-- Return overall order information plus a row per line item with specifics of the order.

CREATE PROCEDURE usp_OrdersDetail
(
    @OrderID    int,
    @Culture nvarchar(10),
    @OrderDate  datetime OUTPUT,
    @ShipDate   datetime OUTPUT,
    @OrderTotal nvarchar(20) OUTPUT
)
AS
SET NOCOUNT ON;

-- Return the order dates from the Orders
--   Also verifies the order exists for this customer. 

SELECT 
    @OrderDate = OrderDate,
    @ShipDate = ShipDate
FROM    
    Sales.SalesOrderHeader
WHERE   
    SalesOrderID = @OrderID;

IF @@Rowcount = 1
BEGIN

/* First, return the OrderTotal out param */
SELECT  
    @OrderTotal = dbo.ConvertCurrency(SUM(SOD.OrderQty * SOD.UnitPrice), @Culture, GetDate()).ToString()
FROM    
    Sales.SalesOrderDetail AS SOD
WHERE   
    SalesOrderID= @OrderID;

/* Then, return the recordset of info */
SELECT  
    P.ProductID, 
    P.[Name],
    P.ProductNumber,
    dbo.ConvertCurrency(SOD.UnitPrice, @Culture, GetDate()).ToString() as UnitPrice,
    SOD.OrderQty,
    dbo.ConvertCurrency(SOD.OrderQty * SOD.UnitPrice, @Culture, GetDate()).ToString() as ExtendedAmount
FROM
    Sales.SalesOrderDetail AS SOD
    INNER JOIN Production.Product as P ON SOD.ProductID = P.ProductID
WHERE   
    SalesOrderID = @OrderID
ORDER BY
      SOD.SalesOrderDetailID;

END
GO


-- Return a table of order summaries for the specified customer

CREATE PROCEDURE usp_OrdersList
(
    @CustomerID int,
    @Culture nvarchar(10)
)
AS
SET NOCOUNT ON;

SELECT  
    SOH.SalesOrderID,
    dbo.ConvertCurrency(sum(SOD.OrderQty*SOD.UnitPrice), @Culture, GetDate()).ToString() as OrderTotal,
    SOH.OrderDate, 
    SOH.ShipDate,
    SCM.StatusMessage
FROM    
    Sales.SalesOrderHeader AS SOH
    INNER JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
    INNER JOIN dbo.StatusCodeMessage AS SCM ON SOH.Status = SCM.StatusCode
GROUP BY    
    SOH.CustomerID, 
    SOH.SalesOrderID, 
    SOH.OrderDate, 
    SOH.ShipDate,
    SCM.StatusMessage
HAVING  
    SOH.CustomerID = @CustomerID;
GO

-- Return all the available shipping methods so the user can pick one.

CREATE PROCEDURE usp_OrdersShippingMethods
AS
SET NOCOUNT ON;

SELECT ShipMethodID, [Name] FROM Purchasing.ShipMethod;
GO

-- Return two result tables, one with all categories and the other with all subcategories
-- of products.  This is used to populate the tree control menu in the UI.

CREATE PROCEDURE usp_ProductCategoryList
AS
SET NOCOUNT ON;

SELECT 
   ProductCategoryID,
   [Name]
FROM 
    Production.ProductCategory
ORDER BY 
    [Name] ASC;

SELECT
    ProductCategoryID,
    ProductSubcategoryID,
    [Name]
FROM
    Production.ProductSubcategory
ORDER BY 
      ProductCategoryID, [Name] ASC;
GO


-- Return information about a particular product as output parameters.
-- TODO: Drop language parameter and create CLR WorldReady class for extracting language from culture
CREATE PROCEDURE usp_ProductDetail
(
    @ProductID    int,
    @Language     nchar(2),
    @Culture      nvarchar (10),
    @ModelNumber  nvarchar (25) OUTPUT,
    @ModelName    nvarchar (50) OUTPUT,
    @ProductImage nvarchar (50) OUTPUT,
    @UnitCost     nvarchar (20) OUTPUT,
    @Description  nvarchar (4000) OUTPUT
)
AS
SET NOCOUNT ON;

SELECT TOP 1
    @ModelNumber  = P.ProductNumber,
    @ModelName    = P.[Name],
    @ProductImage = PP.LargePhotoFileName,
    @UnitCost     = dbo.ConvertCurrency(P.ListPrice, @Culture, GetDate()).ToString(),
    @Description  = PD.Description
FROM 
    Production.Product AS P
    LEFT OUTER JOIN Production.ProductProductPhoto AS PPP on P.ProductID=PPP.ProductID
    LEFT OUTER JOIN Production.ProductPhoto AS PP ON PP.ProductPhotoID=PPP.ProductPhotoID
    LEFT OUTER JOIN Production.ProductModel AS PM ON P.ProductModelID=PM.ProductModelID
    LEFT OUTER JOIN Production.ProductModelProductDescriptionCulture AS PMPDL 
        ON (PM.ProductModelID=PMPDL.ProductModelID AND PMPDL.CultureID=@Language)
    LEFT OUTER JOIN Production.ProductDescription AS PD
        ON PMPDL.ProductDescriptionID=PD.ProductDescriptionID
WHERE 
    P.ProductID = @ProductID AND PPP.[Primary] = 1
ORDER BY PP.ModifiedDate ASC;
GO


-- Search name, product numbers, and descriptions for the specified search text, and return
-- a table containing infomration about the products found which match that search criteria.
CREATE PROCEDURE usp_ProductSearch
(
   @Search nvarchar(255),
   @Language nchar(2),
   @Culture nvarchar(10)
)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

SELECT 
    P.ProductID,
    P.[Name],
    P.ProductNumber,
    dbo.ConvertCurrency(P.ListPrice, @Culture, GetDate()).ToString() as ListPrice, 
    PP.ThumbnailPhotoFileName
FROM 
    Production.Product AS P
    LEFT OUTER JOIN Production.ProductProductPhoto AS PPP on P.ProductID=PPP.ProductID
    LEFT OUTER JOIN Production.ProductPhoto AS PP ON PP.ProductPhotoID=PPP.ProductPhotoID
    LEFT OUTER JOIN Production.ProductModel AS PM ON P.ProductModelID=PM.ProductModelID
    LEFT OUTER JOIN Production.ProductModelProductDescriptionCulture AS PMPDL 
        ON (PM.ProductModelID=PMPDL.ProductModelID AND PMPDL.CultureID=@Language)
    LEFT OUTER JOIN Production.ProductDescription AS PD
        ON PMPDL.ProductDescriptionID=PD.ProductDescriptionID
WHERE 
   ( P.[Name] LIKE N'%' + @Search + N'%' 
   OR
    P.ProductNumber LIKE N'%' + @Search + N'%'
   OR
    PD.[Description] LIKE N'%' + @Search + N'%' )
    AND P.ListPrice IS NOT NULL
    AND P.FinishedGoodsFlag = 1
    AND PPP.[Primary] = 1;
GO


-- Return a table of product information for all products in a given Subcategory.  This is
-- invoked when the user clicks on a leaf node of the tree control menu.

CREATE PROCEDURE usp_ProductsBySubcategory
(
    @SubcategoryID int,
    @Culture nvarchar(10) 
)
AS
SET NOCOUNT ON;

SELECT 
    P.ProductID,
    P.[Name],
    dbo.ConvertCurrency(P.ListPrice, @Culture, GetDate()).ToString() as ListPrice,
    PP.ThumbnailPhotoFileName,
    PSC.ProductCategoryID
FROM 
      Production.Product AS P 
    LEFT OUTER JOIN Production.ProductSubcategory AS PSC on P.ProductSubcategoryID = PSC.ProductSubcategoryID
    LEFT OUTER JOIN Production.ProductProductPhoto AS PPP on P.ProductID=PPP.ProductID
    LEFT OUTER JOIN Production.ProductPhoto AS PP ON PP.ProductPhotoID=PPP.ProductPhotoID
WHERE 
    P.ProductSubcategoryID = @SubcategoryID 
    AND P.ListPrice IS NOT NULL  
    AND P.FinishedGoodsFlag = 1
    AND (PPP.ProductID IS NULL OR PPP.[Primary] = 1 )
ORDER BY 
    P.[Name], 
    P.ProductID,
    PPP.ModifiedDate;
GO


-- Return the five most purchased items in the database in the last 1000 orders.  This is used on the home page to
-- communicate what has been most valued by existing customers.

CREATE PROCEDURE usp_ProductsMostPopular
AS
SET NOCOUNT ON;

SELECT TOP 5 
    SOD.ProductID, 
    SUM(SOD.OrderQty) as TotalNum, 
    P.[Name]
FROM    
    Sales.SalesOrderDetail AS SOD
  INNER JOIN Production.Product AS P ON SOD.ProductID=P.ProductID
WHERE
    SOD.SalesOrderID IN (SELECT TOP 1000 SO.SalesOrderID From Sales.SalesOrderHeader AS SO ORDER BY SO.OrderDate DESC)
GROUP BY 
    SOD.ProductID, 
    P.[Name]
ORDER BY 
    TotalNum DESC;
GO


-- Store a product review in the database.  Activated by the submit button in the ReviewAdd.aspx page.

CREATE PROCEDURE usp_ReviewsAdd
(
    @ProductID     int,
    @CustomerName  nvarchar(50),
    @CustomerEmail nvarchar(50),
    @Rating        int,
    @Comments      nvarchar(3850),
    @ReviewID      int OUTPUT
)
AS
SET NOCOUNT ON;

INSERT INTO Production.ProductReview
(
    ProductID, 
    ReviewerName, 
    EmailAddress, 
    Rating, 
    Comments
)
VALUES
(
    @ProductID, 
    @CustomerName, 
    @CustomerEmail, 
    @Rating, 
    @Comments
);

SELECT 
    @ReviewID = SCOPE_IDENTITY();

GO


-- Return a table of information about product reviews for the specified product.

CREATE PROCEDURE usp_ReviewsList
(
    @ProductID int
)
AS
SET NOCOUNT ON;

SELECT 
    ProductReviewID, 
    ReviewerName, 
    Rating, 
    Comments
FROM 
    Production.ProductReview
WHERE 
    ProductID = @ProductID;
GO

CREATE PROCEDURE usp_ReviewsTopID
AS
SET NOCOUNT ON;

SELECT TOP 1 ProductReviewID FROM Production.ProductReview ORDER BY ProductReviewID DESC;
GO

CREATE PROCEDURE usp_ReviewsLatest
(
    @ProductReviewID int
)
AS
SET NOCOUNT ON;

SELECT PR.ProductReviewID, PR.Rating, P.[Name] as ProductName, PR.ReviewerName as Reviewer, PR.ReviewDate, PR.Comments 
FROM Production.ProductReview AS PR 
LEFT OUTER JOIN Production.Product AS P ON PR.ProductID = P.ProductID
WHERE PR.ProductReviewID > @ProductReviewID;
GO



-- Place a new product in a shopping cart, or increase the quantity of
-- a product already in the shopping cart.

CREATE PROCEDURE usp_ShoppingCartAddItem
(
    @CartID nvarchar(50),
    @ProductID int,
    @Quantity int
)
AS
SET NOCOUNT ON;

DECLARE @CountItems int

SELECT
    @CountItems = Count(ProductID)
FROM
    Sales.ShoppingCartItem
WHERE
    ProductID = @ProductID
  AND
    ShoppingCartID = @CartID;

IF @CountItems > 0  -- There are items - update the current quantity 
    UPDATE
        Sales.ShoppingCartItem
    SET
        Quantity = (@Quantity + Quantity)
    WHERE
        ProductID = @ProductID
      AND
        ShoppingCartID = @CartID;
ELSE  -- New entry for this Cart.  Add a new record 
    BEGIN
    INSERT INTO Sales.ShoppingCartItem
    (
        ShoppingCartID,
        Quantity,
        ProductID
    )
    VALUES
    (
        @CartID,
        @Quantity,
        @ProductID
    );
    END
GO

-- Remove all entries in the specified shopping cart.

CREATE PROCEDURE usp_ShoppingCartEmpty
(
    @CartID nvarchar(50)
)
AS
SET NOCOUNT ON;

DELETE FROM Sales.ShoppingCartItem
WHERE 
    ShoppingCartID = @CartID;
GO


-- Copy the contents of the shopping cart into a Sales Order.  Activated via the submit button on the CheckOut.aspx page.

CREATE PROCEDURE usp_OrdersAdd
(
    @CustomerID     int,
    @CartID         nvarchar(50),
    @OrderDate      datetime,        
    @ShipDate       datetime,
    @ShippingMethod int,
    @Status         int,
    @BillToAddressID    int,
    @ShipToAddressID    int,
    @OrderID        int OUTPUT
)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

BEGIN TRANSACTION AddOrder;
DECLARE @err int
DECLARE @rc int
DECLARE @ContactID int

DECLARE @OrderTotal money
SELECT 
       @OrderTotal = Cast(sum(SCI.Quantity*P.ListPrice) as money) 
FROM 
     Sales.ShoppingCartItem as SCI
LEFT OUTER JOIN
    Production.Product as P ON SCI.ProductID = P.ProductID
WHERE
    SCI.ShoppingCartID = @CartID;

SET @err = @@error;
IF (@err <> 0) GOTO abort

SELECT
    @ContactID = ContactID
FROM 
    Sales.Individual
WHERE 
    CustomerID = @CustomerID;

SET @err = @@error;
SET @rc = @@rowcount;

if (@err <> 0 OR @rc < 1) GOTO abort
    
-- TODO: Come up with a better tax and freight story in a future release.

-- Create the Order header 
INSERT INTO Sales.SalesOrderHeader
(
    CustomerID, 
    SubTotal,
    TaxAmt,
    Freight,
    OrderDate,
    DueDate, 
    ShipDate,
    ShipMethodID,
    ContactID,
    OnlineOrderFlag,
    Status,
    BillToAddressID,
    ShipToAddressID
)
VALUES
(   
    @CustomerID, 
    @OrderTotal,
    0,
    cast (@OrderTotal * .1 AS money),
    @OrderDate, 
    @ShipDate,
    @ShipDate,
    @ShippingMethod,
    @ContactID,
    1,
    @Status,
    @BillToAddressID,
    @ShipToAddressID
);

SELECT
    @OrderID = SCOPE_IDENTITY(),
    @err = @@error;

IF (@err <> 0) GOTO abort



-- Copy items from given shopping cart to SalesOrderDetail table for given OrderID

INSERT INTO Sales.SalesOrderDetail
      (
    SalesOrderID, 
    ProductID, 
    SpecialOfferID,
    OrderQty, 
    UnitPrice
      )
    (SELECT @OrderID, 
           SCI.ProductID, 
           1, -- No discount
           SCI.Quantity, 
           P.ListPrice
    FROM 
         Sales.ShoppingCartItem as SCI
    INNER JOIN Production.Product as P ON SCI.ProductID = P.ProductID
    WHERE 
          SCI.ShoppingCartID = @CartID)

       SET @err = @@error;
       IF (@err <> 0) GOTO abort

EXEC usp_ShoppingCartEmpty @CartID;

COMMIT TRANSACTION AddOrder;
RETURN (0);

abort:
ROLLBACK TRANSACTION AddOrder
RETURN (@err);

GO


-- Return the number of entries in the shopping cart

CREATE PROCEDURE usp_ShoppingCartItemCount
(
    @CartID    nvarchar(50),
    @ItemCount int OUTPUT
)
AS
SET NOCOUNT ON;

SELECT 
    @ItemCount = COUNT(ProductID)
FROM 
    Sales.ShoppingCartItem 
WHERE 
    ShoppingCartID = @CartID;
GO

-- Return a list of the five most recent models added or modified in the shopping cart
CREATE PROCEDURE usp_ShoppingCartListTopModels 
(
    @CartID nvarchar(50)
)
AS
SET NOCOUNT ON;

SELECT TOP 5
    PM.[Name], max(SCI.ModifiedDate)
FROM Production.ProductModel AS PM
    INNER JOIN Production.Product AS P ON P.ProductModelID = PM.ProductModelID
    INNER JOIN Sales.ShoppingCartItem AS SCI ON P.ProductID = SCI.ProductID
WHERE SCI.ShoppingCartID = @CartID 
GROUP BY PM.[Name]
ORDER BY max(SCI.ModifiedDate) DESC;
GO

CREATE PROCEDURE usp_ShoppingCartGetProductRecommendations
(
    @CartID nvarchar(50)
)
AS EXTERNAL NAME [StoreServerComponents].[Microsoft.Samples.SqlServer.StoreServerComponents].[GetProductRecommendations];
GO

-- Return a table containing information about all the entries in the shopping cart.  Used to display the contents
-- of the shopping cart.

CREATE PROCEDURE usp_ShoppingList
(
    @CartID nvarchar(50),
    @Culture nvarchar(10)
)
AS
SET NOCOUNT ON;

SELECT 
    P.ProductID, 
    P.[Name],
    P.ProductNumber,
    SCI.Quantity,
    dbo.ConvertCurrency(P.ListPrice, @Culture, GetDate()).ToString() as ListPrice,
    dbo.ConvertCurrency(P.ListPrice * SCI.Quantity, @Culture, GetDate()).ToString() as ExtendedAmount
FROM 
    Production.Product as P,
    Sales.ShoppingCartItem as SCI
WHERE 
    P.ProductID = SCI.ProductID
  AND 
    SCI.ShoppingCartID = @CartID
ORDER BY 
    P.[Name],
    P.ProductNumber;
GO


-- Changes the shopping cart ID from a temporary id to a permanent one when the user authenticates.

CREATE PROCEDURE usp_SetShoppingCartID
(
    @OriginalCartId nvarchar(50),
    @NewCartId      nvarchar(50)
)
AS
SET NOCOUNT ON;

UPDATE 
    Sales.ShoppingCartItem
SET 
    ShoppingCartID = @NewCartId 
WHERE 
    ShoppingCartID = @OriginalCartId;
GO


-- Erases shopping carts which have been lying around for 30 days.  Invoked by a SQL Server job.

CREATE PROCEDURE usp_ShoppingCartRemoveAbandoned
AS
SET NOCOUNT ON;

DELETE FROM Sales.ShoppingCartItem
WHERE 
    DATEDIFF(dd, DateCreated, GetDate()) > 30;
GO


-- Deletes an entry from the specified shopping cart.  

CREATE PROCEDURE usp_ShoppingCartRemoveItem
(
    @CartID nvarchar(50),
    @ProductID int
)
AS
SET NOCOUNT ON;

DELETE FROM Sales.ShoppingCartItem
WHERE 
    ShoppingCartID = @CartID
  AND
    ProductID = @ProductID;
GO


-- Returns the total value of items in the shopping cart

CREATE PROCEDURE usp_ShoppingCartTotal
(
    @CartID    nvarchar(50),
    @Culture nvarchar(10),
    @TotalCost nvarchar(20) OUTPUT
)
AS
SET NOCOUNT ON;

SELECT 
    @TotalCost = dbo.ConvertCurrency(SUM(P.ListPrice * SCI.Quantity), @Culture, GetDate()).ToString()
FROM 
    Sales.ShoppingCartItem as SCI,
    Production.Product as P
WHERE
    SCI.ShoppingCartID = @CartID
  AND
    P.ProductID = SCI.ProductID;
GO


-- Changes the total number of ordered items for a particular product in a particular cart.

CREATE PROCEDURE usp_ShoppingCartUpdate
(
    @CartID    nvarchar(50),
    @ProductID int,
    @Quantity  int
)
AS
SET NOCOUNT ON;

UPDATE Sales.ShoppingCartItem
SET 
    Quantity = @Quantity
WHERE 
    ShoppingCartID = @CartID 
  AND 
    ProductID = @ProductID;
GO


----------------------------------------------------------
-- Create tables
----------------------------------------------------------

-- Create the table for storing order state information
IF NOT EXISTS(SELECT * from sysobjects WHERE name=N'OrderState' AND type='U')
    CREATE TABLE OrderState 
    (
            ConversationGroupId         uniqueidentifier not null primary key,
            OriginalDialogHandle        uniqueidentifier,
            ToPurchasingDialogHandle    uniqueidentifier, 
            ToShippingDialogHandle      uniqueidentifier, 
            ToManufacturingDialogHandle uniqueidentifier,
            CustomerId      nvarchar(50),
            SalesOrderId    nvarchar(50),
            OrderDate       nvarchar(50),             
            Status          nvarchar(50) default '0'
    );
go

-- Create the table for storing order item information
IF NOT EXISTS(SELECT * from sysobjects WHERE name=N'ItemState' AND type='U')
    CREATE TABLE ItemState 
    (
            SalesOrderId            int not null,
            ProductId               int not null,
            Type                    nvarchar(50),
            Quantity                int,
            Status                  nvarchar(50) default '0'
    );
go

-- Create a table for storing processing
IF NOT EXISTS(SELECT * from sysobjects WHERE name=N'ProcesssingLog' AND type='U')
    CREATE TABLE ProcessingLog 
    (
            LogId                       int IDENTITY PRIMARY KEY CLUSTERED,
            Information                 nvarchar(max)
    );
go

-- Create the table for storing order status code to string mapping
IF NOT EXISTS(SELECT * FROM sysobjects WHERE name=N'StatusCodeMessage' AND type='U')
    CREATE TABLE StatusCodeMessage 
    (
            StatusCode                      int NOT NULL PRIMARY KEY,
            StatusMessage                   nvarchar(2000)
    );
go

-- Insert status code and corresponding messages 
INSERT INTO StatusCodeMessage VALUES (0, 'Pending inventory check.');
INSERT INTO StatusCodeMessage VALUES (1, 'Pending purchase approval.');
INSERT INTO StatusCodeMessage VALUES (2, 'Pending purchase accept');
INSERT INTO StatusCodeMessage VALUES (3, 'Pending purchase delivery.');
INSERT INTO StatusCodeMessage VALUES (4, 'Pening manufacturing accept.');
INSERT INTO StatusCodeMessage VALUES (5, 'Pending manufacturing delivery.');
INSERT INTO StatusCodeMessage VALUES (6, 'Pending shipping accept.');
INSERT INTO StatusCodeMessage VALUES (7, 'Shipped');
INSERT INTO StatusCodeMessage VALUES (8, 'Illegal state');


-- Create the table for storing all sent and received messages
CREATE TABLE OrderingAudit
    (
        OrderingAuditID int IDENTITY PRIMARY KEY,
        TimeStamp datetime, -- time stamp of the message
        Direction nvarchar(20),
        MessageBody xml
    );
GO

----------------------------------------------------------
-- Create stored procedures
----------------------------------------------------------

/* Returns the stock level for the product. This function is used internally only */
CREATE FUNCTION GetStock
(
    @ProductID      int
)
RETURNS int
AS
BEGIN
DECLARE @ret  int
-- A product is in 2 locations: a storage/warehouse location and 
-- amanufacturing/work center location. Only look at the former location
-- to deterime stock level since the later is for in production use only.
SELECT @ret = SUM(Quantity)
FROM Production.ProductInventory pi
WHERE pi.ProductID=@ProductID 
AND LocationID BETWEEN 1 and 7 -- 
GROUP BY ProductID

IF (@ret IS NULL) 
    SET @ret = 0;
RETURN @ret;
END
GO

/* Returns the quantity of the product in the inventory */
CREATE PROCEDURE GetStockLevel
(
    @ProductID int,
    @Quantity int OUTPUT
)
AS
SET NOCOUNT ON;

SELECT 
    @Quantity = dbo.GetStock(@ProductID);
GO

/* Debits the quantity of the product in the inventory */
CREATE PROCEDURE DebitStockLevel
(
    @ProductID int,
    @Quantity int 
)
AS
SET NOCOUNT ON;

UPDATE 
    Production.ProductInventory
SET 
    Quantity = Quantity-@Quantity
WHERE 
    ProductID = @ProductID
    AND LocationID = '6';
GO

/* 
 * Returns the make flag of the product. A value of 1 indicates
 * a make item.
 */
CREATE PROCEDURE GetMakeFlag
(
    @ProductID int,
    @MakeFlag int OUTPUT
)
AS
SET NOCOUNT ON;

SELECT 
    @MakeFlag = MakeFlag
FROM 
    Production.Product
WHERE 
    ProductID = @ProductID;
GO

/* Returns the total dollar amount of everything in the shopping cart */
CREATE PROCEDURE GetShoppingCartTotal
(
    @CartID         nvarchar(50)
)
AS
SET NOCOUNT ON;

SELECT 
    Cast(sum(ShoppingCart.Quantity*Product.ListPrice) as int) 
FROM 
     ShoppingCart
LEFT OUTER JOIN
    Product ON ShoppingCart.ProductID = Product.ProductID
WHERE
    ShoppingCart.ShoppingCartID = @CartID;

DECLARE @err int
SET @err = @@error;
IF (@err <> 0) GOTO abort

abort: 
ROLLBACK TRANSACTION;
RETURN(@err)
GO


CREATE PROCEDURE GetShoppingCartList
(
    @CartID         nvarchar(50)
)
AS
SET NOCOUNT ON;

SELECT 
    ShoppingCart.ProductID, ShoppingCart.Quantity, Product.ListPrice 
FROM 
    ShoppingCart 
    INNER JOIN Product ON ShoppingCart.ProductID = Product.ProductID 
WHERE 
    ShoppingCart.ShoppingCartID=@CartID;

DECLARE @err int
SET @err = @@error
IF (@err <> 0) GOTO abort

abort: 
ROLLBACK TRANSACTION;
RETURN(@err);
GO

/* Updates the status of the order */
CREATE PROCEDURE UpdateOrderStatus
(
    @SalesOrderID       int,
    @Status             int
)
AS
SET NOCOUNT ON;

UPDATE
        Sales.SalesOrderHeader
SET
        Status = @Status
WHERE
        SalesOrderID = @SalesOrderID;
GO

/* Gets the customer information */
CREATE PROCEDURE GetCustomerInfo
(
    @CustomerID     int
)
AS
SET NOCOUNT ON;

SELECT TOP 1
     CN.FirstName, CN.LastName, A.AddressLine1, A.AddressLine2, A.City, SP.Name as State, A.PostalCode, CR.Name as Country, CN.Phone as Telephone
FROM 
    Sales.Customer as C 
    INNER JOIN Sales.Individual AS I ON C.CustomerID = I.CustomerID
    LEFT OUTER JOIN Person.Contact as CN ON I.ContactID = CN.ContactID
    LEFT OUTER JOIN Sales.CustomerAddress as CA ON C.CustomerID = CA.CustomerID
    LEFT OUTER JOIN Person.Address as A ON CA.AddressID = A.AddressID
    LEFT OUTER JOIN Person.StateProvince as SP on A.StateProvinceID = SP.StateProvinceID
    LEFT OUTER JOIN Person.CountryRegion as CR ON SP.CountryRegionCode = CR.CountryRegionCode
WHERE 
    C.CustomerID=@CustomerID;
GO

/* Updates the state if the state information exist, else adds a new state record */
CREATE PROCEDURE UpdateOrderState
(
    @ConversationGroupId            uniqueidentifier, 
    @OriginalDialogHandle       uniqueidentifier,
    @ToPurchasingDialogHandle   uniqueidentifier, 
    @ToShippingDialogHandle     uniqueidentifier,
    @ToManufacturingDialogHandle        uniqueidentifier,
    @CustomerId                 nvarchar(50),
    @SalesOrderId               nvarchar(50),
    @OrderDate                  nvarchar(50), 
    @Status                     nvarchar(50)
)
AS
SET NOCOUNT ON;

DECLARE @Count int

SELECT
    @Count = Count(*)
FROM
    OrderState
WHERE
    ConversationGroupId = @ConversationGroupId

IF @Count > 0 -- record exist
BEGIN
    UPDATE OrderState 
    SET
        OriginalDialogHandle = @OriginalDialogHandle,       
        ToPurchasingDialogHandle = @ToPurchasingDialogHandle,   
        ToShippingDialogHandle = @ToShippingDialogHandle,       
        ToManufacturingDialogHandle = @ToManufacturingDialogHandle,
        CustomerId = @CustomerId,               
        SalesOrderId = @SalesOrderId,              
        OrderDate = @OrderDate,              
        Status = @Status
    WHERE 
        ConversationGroupId = @ConversationGroupId;
END
ELSE  /* New entry for this Cart.  Add a new record */
BEGIN

    INSERT INTO OrderState
    (
        ConversationGroupId,
        OriginalDialogHandle,       
        ToPurchasingDialogHandle,   
        ToShippingDialogHandle, 
        ToManufacturingDialogHandle,
        CustomerId,             
        SalesOrderId,              
        OrderDate,           
        Status
    )
    VALUES
    (
        @ConversationGroupId,
        @OriginalDialogHandle,      
        @ToPurchasingDialogHandle,  
        @ToShippingDialogHandle,    
        @ToManufacturingDialogHandle,
        @CustomerId,                
        @SalesOrderId,             
        @OrderDate,          
        @Status
    );
END

GO

/* Updates the state of the list item if exist, else adds a new state record */
CREATE PROCEDURE UpdateItemState
(
    @SalesOrderId                   int,
    @ProductId                      int,
    @Type                           nvarchar(50),
    @Quantity                       int,
    @Status                         nvarchar(50)
)
AS
SET NOCOUNT ON;

DECLARE @Count int

SELECT
    @Count = Count(*)
FROM
    ItemState
WHERE
    SalesOrderId = @SalesOrderId 
AND
    ProductId = @ProductId
AND
    Type = @Type

IF @Count > 0 -- record exist
BEGIN
    UPDATE 
        ItemState
    SET
        Quantity = @Quantity,       
        Status = @Status
    WHERE 
        SalesOrderId = @SalesOrderId 
    AND
        ProductId = @ProductId
    AND
        Type = @Type
END
ELSE  /* New entry */
BEGIN

    INSERT INTO ItemState
    (
        SalesOrderId,
        ProductId,  
        Type,          
        Quantity,
        Status
    )
    VALUES
    (
        @SalesOrderId,
        @ProductId,
        @Type,
        @Quantity,
        @Status
    );
END
GO

CREATE PROCEDURE GetOrderState
(
    @ConversationGroupId            uniqueidentifier 
)
AS
SET NOCOUNT ON;

SELECT
    OriginalDialogHandle,
    ToPurchasingDialogHandle,
    ToShippingDialogHandle,
    ToManufacturingDialogHandle,    
    CustomerId,         
    SalesOrderId,    
    OrderDate,                  
    Status 
FROM
     OrderState 
WHERE 
    ConversationGroupId = @ConversationGroupId;
GO


CREATE PROCEDURE GetItemState
(
    @SalesOrderId           int
)
AS
SET NOCOUNT ON;

SELECT
    ProductId, 
    Type,
    Quantity,
    Status 
FROM
     ItemState 
WHERE 
    SalesOrderId = @SalesOrderId;
GO


CREATE PROCEDURE StoreProcessingInfo
(
    @Information                nvarchar(max)
)
AS
SET NOCOUNT ON;

INSERT INTO ProcessingLog
    (
        Information
    )
    VALUES
    (
        @Information
    );
GO

CREATE PROCEDURE StoreMessage
(
    @MessageBody            xml,
    @Direction              nvarchar(20)
)
AS
SET NOCOUNT ON;

DECLARE @CurrentDate datetime 
DECLARE @Message xml
SET @CurrentDate = current_timestamp

SET @Message = CAST(@MessageBody as XML);

INSERT INTO OrderingAudit
    (
        TimeStamp,
        Direction, 
        MessageBody
    )
    VALUES
    (
        @CurrentDate,
        @Direction,
        @Message        
    );
GO

--
-- Security grants section
--

-- Capture the OS version in a temp table so that we can pick 
-- the correct windows account to grant access to for the objects 
-- defined in this file.  This enables ASP.NET programs to access 
-- these objects.
CREATE TABLE #GetVersionValues
(
	[Index]	int,
	[Name]	sysname,
	Internal_value	int,
	Character_Value	sysname
);
GO

INSERT #GetVersionValues
EXEC master.dbo.xp_msver 'WindowsVersion';
GO

DECLARE @OSVersion decimal(9, 2);
DECLARE @ASPUserName nvarchar(100);
DECLARE @NetworkService nvarchar(100);

-- For globalization purposes, the sample install script uses a SID to set NT AUTHORITY\NETWORK SERVICE
-- SELECT SUSER_SID('NT AUTHORITY\NETWORK SERVICE') returns the English SID. 
SET @NetworkService = SUSER_SNAME(0x010100000000000514000000);

SELECT @OSVersion = CONVERT(decimal(9, 2), SUBSTRING(Character_Value, 1, CHARINDEX(' ', Character_Value) - 1)) 
FROM #GetVersionValues;

-- IIS6 uses a different account to run web apps under.  II6 first shipped with Windows 2003 (v5.2).
IF (@OSVersion < 5.20) 
	SET @ASPUserName = CONVERT(nvarchar(64), SERVERPROPERTY('machinename')) + N'\ASPNET';
ELSE
	SET @ASPUserName = @NetworkService;

IF NOT EXISTS(SELECT principal_id FROM sys.server_principals where name = @ASPUserName)
BEGIN
    EXEC sp_grantlogin @ASPUserName;
END

IF NOT EXISTS(SELECT principal_id from sys.database_principals where name = @ASPUserName)
BEGIN
    EXEC sp_grantdbaccess @ASPUserName;
END


EXEC('GRANT EXECUTE ON dbo.usp_CustomerAdd TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_CustomerAddSurvey TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_CustomerUpdate TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_CustomerAlsoBought TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_CustomerDetail TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_CustomerLogin TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_CustomerAllStateProvince TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_CustomerDefaultAddresses TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_CustomerAccessAddress TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_OrdersAdd TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_OrdersAddresses TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_OrdersDetail TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_OrdersList TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON usp_OrdersShippingMethods TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ProductCategoryList TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ProductDetail TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ProductSearch TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ProductsBySubcategory TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ProductsMostPopular TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ReviewsAdd TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ReviewsList TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingCartAddItem TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingCartEmpty TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingCartItemCount TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingCartListTopModels TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingCartGetProductRecommendations TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingList TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_SetShoppingCartID TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingCartRemoveAbandoned TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingCartRemoveItem TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingCartTotal TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON dbo.usp_ShoppingCartUpdate TO [' + @ASPUserName + ']');

EXEC('GRANT SELECT ON Production.ProductModel TO [' + @ASPUserName + ']');

EXEC('GRANT SELECT ON Production.Product TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[UpdateOrderStatus] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[GetShoppingCartTotal] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[GetShoppingCartList] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[GetStock] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[DebitStockLevel] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[GetStockLevel] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[GetMakeFlag] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[GetCustomerInfo] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[GetOrderState] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[UpdateOrderState] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[GetItemState] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[UpdateItemState] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON [dbo].[StoreProcessingInfo] TO [' + @ASPUserName + ']');


-- ****************************************
-- Grant EXECUTE acccess to XML Schemas
-- ****************************************
EXEC('GRANT EXECUTE ON XML SCHEMA Collection::[Person].[AdditionalContactInfoSchemaCollection] TO [' + @ASPUserName + ']');


EXEC('GRANT EXECUTE ON XML SCHEMA Collection::[Sales].[StoreSurveySchemaCollection] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON XML SCHEMA Collection::[Sales].[IndividualSurveySchemaCollection] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON XML SCHEMA Collection::[Production].[ProductDescriptionSchemaCollection] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON XML SCHEMA Collection::[Production].[ManuInstructionsSchemaCollection] TO [' + @ASPUserName + ']');

EXEC('GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [' + @ASPUserName + ']');

EXEC('GRANT RECEIVE ON QueryNotificationErrorsQueue TO [' + @ASPUserName + ']');

GO

DROP TABLE #GetVersionValues;
GO

-- ReviewWatcher GRANTS

DECLARE @UserName nvarchar(128)
SET @UserName = SUSER_SNAME()

IF NOT EXISTS(SELECT principal_id FROM sys.server_principals where name = @UserName)
BEGIN
    EXEC sp_grantlogin @UserName;
END

IF NOT EXISTS(SELECT principal_id from sys.database_principals where name = @UserName)
BEGIN
    EXEC sp_grantdbaccess @UserName;
END


GO



EXEC sp_changedbowner 'sa';

GO






--
-- end make objects
-- 


-- =======================================================
-- ADD SCHEDULED JOBS TO Adventure Works Cycles DB
-- =======================================================

DECLARE @JobCount int

USE msdb

SELECT @JobCount=Count(name) FROM sysjobs WHERE name='RemoveAbandonedCarts';

IF @JobCount<>0 EXEC sp_delete_job @job_name='RemoveAbandonedCarts'; 

EXEC sp_add_job
    @job_name = 'RemoveAbandonedCarts',
    @enabled = 1,
    @notify_level_eventlog = 3;

EXEC sp_add_jobserver
    @job_name = 'RemoveAbandonedCarts',
    @server_name = "(local)";

EXEC sp_add_jobstep
    @job_name = 'RemoveAbandonedCarts',
    @step_name = 'Step 1',
    @command = "exec usp_ShoppingCartRemoveAbandoned",
    @database_name = 'AdventureWorks',
    @retry_attempts = 2,
    @retry_interval = 5;

EXEC sp_add_jobschedule
    @job_name = 'RemoveAbandonedCarts',
    @name = 'Every day at 12AM',
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 1;

--
-- end scheduled jobs
-- 

USE AdventureWorks
GO






